package com.cn.ccp.service.course;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.bean.CourseIssueBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;

public class CourseIssueService extends BasicService {
	
	private Log log = LogFactory.getLog(CourseIssueService.class);
	public int getStudentCount(String courseid){
		String sql = "SELECT count(id) FROM course_issue ci "+
						" WHERE ci.course_id = ?  and ci.issue_type = 1";
		try {
			return jdbcTemplate.queryForInt(sql ,new Object[]{courseid});
		} catch (Exception e) {
		}
		return 0 ;
	}
	
	public int getTeacherCount(String courseid){
		String sql = "SELECT count(1) FROM course_issue ci "+
						" LEFT OUTER JOIN USER u ON u.id = ci.student_id "+
						" LEFT OUTER JOIN USER tea ON tea.id = ci.teacher_id "+
						" WHERE ci.course_id = ?  and ci.issue_type = 1 ";
			try {
				return jdbcTemplate.queryForInt(sql ,new Object[]{courseid});
			} catch (Exception e) {
			}
			return 0 ;
		
	}

	
	public boolean saveStudent(String courseId , String content){
		String sql = "INSERT INTO course_issue "+
					" (course_id,student_id,issue_type,issue,write_time)VALUES(?,?,1,?,CURRENT_TIMESTAMP);";
		Object[] objs = new Object[]{courseId  ,CCPContants.getCurrUserId(),content };
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public boolean saveTeacher(String courseId , String studentId , String contenbt ,int id ){
		String sql = "INSERT INTO course_issue "+
					" (course_id,student_id ,teacher_id, issue_type,issue,write_time ,issue_id)VALUES(?,?,?,2,?,CURRENT_TIMESTAMP ,?);";
		Object[] objs = new Object[]{courseId ,studentId,CCPContants.getCurrUserId(),contenbt,id};
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public List<CourseIssueBean> getStudentCourseIssueList(String courseid, String type ,int start, int pagesize){
		String sql = "SELECT ci.* ,u.username as studentName ,tea.username as teacherName FROM course_issue ci "+
					" LEFT OUTER JOIN USER u ON u.id = ci.student_id "+
					" LEFT OUTER JOIN USER tea ON tea.id = ci.teacher_id "+
					" WHERE ci.course_id = ?   and ci.issue_type = ? "+
					" ORDER BY ci.issue_id ,ci.write_time DESC LIMIT ?,?";
	    return jdbcTemplate.query(sql, new Object[]{courseid , type ,start,pagesize }, new CourseIssueRowMapper());
	}
	
	public List<CourseIssueBean> getTeacherCourseIssueListForStudentQuestion(String courseid ,String type, int start, int pagesize){
		String sql = "SELECT ci.* ,u.username as studentName ,tea.username as teacherName FROM course_issue ci "+
				" LEFT OUTER JOIN USER u ON u.id = ci.student_id "+
				" LEFT OUTER JOIN USER tea ON tea.id = ci.teacher_id "+
				" WHERE ci.course_id = ?  and ci.issue_type = ? "+
				" ORDER BY ci.issue_id ,ci.write_time DESC LIMIT ?,?";
		return jdbcTemplate.query(sql, new Object[]{courseid ,  type ,start,pagesize }, new CourseIssueRowMapper());
	}
	
	public List<CourseIssueBean>  getAnswerById(String issueIds){
		String sql = "select * from course_issue where issue_id in ( "+issueIds+" ) ORDER BY write_time DESC ";
		log.info(sql);
		return jdbcTemplate.query(sql, new CourseIssueRowMapper());
	}
	
	public boolean  delAnswerById(String id){
		String sql = "delete  from course_issue where id = ?";
		return jdbcTemplate.update(sql,new Object[]{id} ) > 0;
	}
	
	
	public class CourseIssueRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseIssueBean bean = new CourseIssueBean();
			bean.setId(rs.getInt("id"));
			bean.setCourse_id(rs.getString("course_id"));
			try{
				bean.setStudentName(rs.getString("studentName"));
			}catch(Exception e){}
			try{
				bean.setTeacherName(rs.getString("teacherName"));
			}catch(Exception e){}
			bean.setStudent_id(rs.getString("student_id"));
			bean.setTeacher_id(rs.getString("teacher_id"));
			bean.setIssue_type(rs.getString("issue_type"));
			bean.setIssue(rs.getString("issue"));
			bean.setWrite_time(rs.getString("write_time").substring(0,19));
			bean.setIssue_id(rs.getString("issue_id"));
			return bean;
		}
	}
}
